﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aspose.Cells;
using System.Drawing;
using System.Windows.Forms;

namespace CodeGenerator.Core
{
    public class ExcelTool
    {
        /// <summary>
        /// 创建 Excel Sheet结构体
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheetName"></param>
        /// <param name="head"></param>
        public static void CreateWorkSheets(Workbook workbook, string sheetName, string head, string url)
        {
            //Worksheet sheet = workbook.Worksheets.Add(sheetName.Length <= 31 ? sheetName : sheetName.Substring(1, 31)); //workbook.Worksheets[0]; //工作表
          //  workbook.Worksheets.RemoveAt("Sheet1");
           // Worksheet sheet = workbook.Worksheets.Add("index");
            Worksheet sheet = workbook.Worksheets[1];//查看文档的sheet1内容 

            Cells cells = sheet.Cells;//单元格
            int rowCount = cells.MaxDataRow + 1;//当Excel没有一行数据时，读取到的cells.MaxDataRow=-1，当有一行数据时cells.MaxDataRow=0     MaxDataRow：包含数据的单元格的最大行索引  
           
            int headRowCount = rowCount + 1;
           
          

            Style style = workbook.Styles[workbook.Styles.Add()];//新增样式
            style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;

            style.HorizontalAlignment = TextAlignmentType.Center;//文字居中  
            style.Font.Name = "宋体";//文字字体 

            #region 表头
            //标题 
            style.Font.Size = 18;//文字大小  
            style.Font.IsBold = true;//粗体

            cells.Merge(rowCount, 0, 1, 9);               //合并单元格 
            cells[rowCount, 0].PutValue(head);   //填写内容 
            cells[rowCount, 0].SetStyle(style);            //给单元格关联样式  
            cells[rowCount, 0].GetStyle().ForegroundColor = Color.Yellow;
            cells[rowCount, 0].GetStyle().Pattern = BackgroundType.Solid;
            cells.SetRowHeight(rowCount, 35);              //设置行高
            sheet.Hyperlinks.Add(rowCount, 0, 1, 1, url);

            style.Font.Size = 13;
            style.Font.IsBold = true;
            style.ForegroundColor = Color.Cyan;
            style.Pattern = BackgroundType.Solid;
            //cells[0, 0].SetStyle(style); 

          //  ExcelTool.CreateWordAndLink(workbook, head, rowCount, 0, "#" + head + "!A" + rowCount);
            #endregion
            #region 表格
            #region 表格标题行
            // 序号
            style.HorizontalAlignment = TextAlignmentType.Center;
            cells[headRowCount, 0].PutValue("序号");
            cells[headRowCount, 0].SetStyle(style);
            cells.SetRowHeight(2, 20);
            cells.SetColumnWidthPixel(0, 38);
            // 列名
            cells[headRowCount, 1].PutValue("列名");
            cells[headRowCount, 1].SetStyle(style);
            cells.SetColumnWidthPixel(1, 120);
            // 数据类型
            cells[headRowCount, 2].PutValue("数据类型");
            cells[headRowCount, 2].SetStyle(style);
            cells.SetColumnWidthPixel(2, 110);
            // 长度
            cells[headRowCount, 3].PutValue("长度");
            cells[headRowCount, 3].SetStyle(style);
            cells.SetColumnWidthPixel(3, 69);
            // 小数位
            cells[headRowCount, 4].PutValue("小数位");
            cells[headRowCount, 4].SetStyle(style);
            cells.SetColumnWidthPixel(4, 70);
            // 允许空
            cells[headRowCount, 5].PutValue("允许空");
            cells[headRowCount, 5].SetStyle(style);
            cells.SetColumnWidthPixel(5, 70);
            // 默认值
            cells[headRowCount, 6].PutValue("默认值");
            cells[headRowCount, 6].SetStyle(style);
            cells.SetColumnWidthPixel(6, 120);
            // 说明
            style.IsTextWrapped = true;//单元格内容自动换行
            cells[headRowCount, 7].PutValue("说明");
            cells[headRowCount, 7].SetStyle(style);
            cells.SetColumnWidthPixel(7, 600);
            // 主键
            cells[headRowCount, 8].PutValue("主键");
            cells[headRowCount, 8].SetStyle(style);
            cells.SetColumnWidthPixel(8, 70);


            
            #endregion
            #endregion
        }

        /// <summary>
        /// 创建首页索引导航工作表
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="head">表头文字</param>
        /// <param name="row">第几行</param>
        /// <param name="col">第几列</param>
        /// <param name="url">超链接</param>
        public static void CreateIndexSheet(Workbook workbook, string head)
        {
            workbook.Worksheets.RemoveAt("Sheet1");
            Worksheet sheet = workbook.Worksheets.Add("表索引");
            workbook.Worksheets.Add("表信息");

            Cells cells = sheet.Cells;//单元格

            Style style = workbook.Styles[workbook.Styles.Add()];//新增样式

            style.HorizontalAlignment = TextAlignmentType.Center;//文字居中  
            style.Font.Name = "宋体";//文字字体 

            //标题 
            style.Font.Size = 18;//文字大小  
            style.Font.IsBold = true;//粗体

            cells[0, 0].PutValue(head);   //填写内容 
            cells[0, 0].SetStyle(style);            //给单元格关联样式  
            cells.SetRowHeight(0, 28);              //设置行高
            cells.SetColumnWidthPixel(0, 600);
        }

        /// <summary>
        /// 创建文字索引
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="word"></param>
        /// <param name="row"></param>
        /// <param name="col"></param>
        /// <param name="url"></param>
        public static void CreateWordAndLink(Workbook workbook, string word, int row, int col)
        {
            Worksheet sheet = workbook.Worksheets[0];
            Cells cells = sheet.Cells;//单元格

            Style style = workbook.Styles[workbook.Styles.Add()];//新增样式

            style.HorizontalAlignment = TextAlignmentType.Center;//文字居中  
            style.Font.Name = "宋体";//文字字体 

            //标题 
            style.Font.Size = 18;//文字大小  
            style.Font.IsBold = true;//粗体

            cells[row, 0].PutValue(word);   //填写内容 
            cells[row, 0].SetStyle(style);            //给单元格关联样式 
            cells.SetRowHeight(row, 35);              //设置行高


            Cells infoCell = workbook.Worksheets[1].Cells;//单元格
            int rowCount = infoCell.MaxDataRow + 2;//当Excel没有一行数据时，读取到的cells.MaxDataRow=-1，当有一行数据时cells.MaxDataRow=0     MaxDataRow：包含数据的单元格的最大行索引  

            sheet.Hyperlinks.Add(row, col, 1, 1, "#表信息!A" + rowCount);
        }

        /// <summary>
        /// 填充数据
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheetName"></param>
        /// <param name="row"></param>
        /// <param name="entity"></param>
        /// <param name="serialNum"></param>
        public static void FillWorkSheetsDate(Workbook workbook, ColumnInfo column, int serialNum)
        {
            Worksheet sheet = workbook.Worksheets[1]; //工作表

            Cells cells = sheet.Cells;//单元格
            int row = cells.MaxDataRow + 1;//当Excel没有一行数据时，读取到的cells.MaxDataRow=-1，当有一行数据时cells.MaxDataRow=0     MaxDataRow：包含数据的单元格的最大行索引  

            Style style = workbook.Styles[workbook.Styles.Add()];//新增样式
            style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.LeftBorder].LineStyle = CellBorderType.Thin;
            style.Borders[BorderType.RightBorder].LineStyle = CellBorderType.Thin;

            style.HorizontalAlignment = TextAlignmentType.Center;
            style.VerticalAlignment = TextAlignmentType.Center;
            style.Font.Name = "宋体";//文字字体 
            style.Font.Size = 11;
            style.Font.IsBold = false;
            cells.SetRowHeight(row, 35);              //设置行高

            // 序号
            cells[row, 0].PutValue(serialNum);
            cells[row, 0].SetStyle(style);
            // 列名
            cells[row, 1].PutValue(column.Name);
            cells[row, 1].SetStyle(style);
            // 数据类型
            cells[row, 2].PutValue(column.DataType);
            cells[row, 2].SetStyle(style);
            // 长度
            cells[row, 3].PutValue(column.MaxLength);
            cells[row, 3].SetStyle(style);
            // 小数位
            cells[row, 4].PutValue(column.DecimalNum);
            cells[row, 4].SetStyle(style);
            // 允许空
            cells[row, 5].PutValue(column.AllowDBNull);
            cells[row, 5].SetStyle(style);
            // 默认值
            cells[row, 6].PutValue(column.DefaultValue);
            cells[row, 6].SetStyle(style);
            // 说明
            style.IsTextWrapped = true;//单元格内容自动换行
            cells[row, 7].PutValue(column.Comments);
            cells[row, 7].SetStyle(style);
            // 主键
            cells[row, 8].PutValue(column.IsPrimaryKey);
            cells[row, 8].SetStyle(style);
        }


        
    }
}
